/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package east2west.dao;

import east2west.dto.SaleCarDTO;
import east2west.util.DatabaseUtil;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author nguyendat
 */
public class SaleCarDAO {

    Connection con = null;
    CallableStatement call = null;
    ResultSet rs = null;

    public ArrayList<SaleCarDTO> listAll() {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("select * from V_SaleCar");
            ArrayList<SaleCarDTO> listSaleCar = new ArrayList<SaleCarDTO>();
            rs = call.executeQuery();
            while (rs.next()) {
                SaleCarDTO dto = new SaleCarDTO();
                dto.setCarId(rs.getInt(1));
                dto.setCarName(rs.getString(2));
                dto.setModeId(rs.getInt(3));
                dto.setTypeId(rs.getInt(4));
                dto.setAirCondition(rs.getBoolean(5));
                dto.setPrice(rs.getFloat(6));
                dto.setModeName(rs.getString(7));
                dto.setTypeName(rs.getString(8));
                dto.setOrderId(rs.getInt(9));
                dto.setPickUpdate(rs.getString(10));
                dto.setDropOffDate(rs.getString(11));
                dto.setOrderDate(rs.getString(12));
                dto.setAmount(rs.getFloat(13));
                dto.setCustId(rs.getInt(14));
                dto.setEmail(rs.getString(15));
                dto.setFullName(rs.getString(16));
                listSaleCar.add(dto);

            }
            return listSaleCar;
        } catch (SQLException ex) {
            return null;
        }

    }

    public ArrayList<SaleCarDTO> listFillter() {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("select CarName,AirConditional,ModelName,TypeName,Price,sum(Amount) as 'Total' From V_SaleCar group by CarName,AirConditional,ModelName,TypeName,Price");
            ArrayList<SaleCarDTO> listFill = new ArrayList<SaleCarDTO>();
            rs = call.executeQuery();
            while (rs.next()) {
                SaleCarDTO dto = new SaleCarDTO();
                dto.setCarName(rs.getString(1));
                //  dto.setAirCondition(rs.getBoolean(2));
                boolean air = rs.getBoolean(2);
                if (air == true) {
                    dto.setTam("Yes");
                } else {
                    dto.setTam("NO");
                }
                dto.setModeName(rs.getString(3));
                dto.setTypeName(rs.getString(4));
                dto.setPrice(rs.getFloat(5));
                dto.setTotal(rs.getFloat("Total"));
                listFill.add(dto);
            }
            return listFill;
        } catch (SQLException ex) {
            return null;
        }

    }
 public ArrayList<SaleCarDTO> listFillterAir() {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("select Distinct(AirConditional) From V_SaleCar");
            ArrayList<SaleCarDTO> listFill = new ArrayList<SaleCarDTO>();
            rs = call.executeQuery();
            while (rs.next()) {
                SaleCarDTO dto = new SaleCarDTO();
             
                //  dto.setAirCondition(rs.getBoolean(2));
                boolean air = rs.getBoolean(1);
                if (air == true) {
                    dto.setTam("Yes");
                } else {
                    dto.setTam("NO");
                }
               
                listFill.add(dto);
            }
            return listFill;
        } catch (SQLException ex) {
            return null;
        }

    }

    public float TotalMode(int id) {
        float sum = 0;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("select sum(Amount) as 'Total' from V_SaleCar where ModelId =?");
            call.setInt(1, id);
            rs = call.executeQuery();
            while (rs.next()) {
                sum = rs.getFloat("Total");
            }
            return sum;
        } catch (SQLException ex) {
            return 0;
        }
    }

    public float TotalType(int id){
          float sum = 0;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("select sum(Amount) as 'Total' from V_SaleCar where TypeId =?");
            call.setInt(1, id);
            rs = call.executeQuery();
            while (rs.next()) {
                sum = rs.getFloat("Total");
            }
            return sum;
        } catch (SQLException ex) {
            return 0;
        }
    }

    public float TotalCost(float price1,float price2){
          float sum = 0;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("select sum(Amount) as 'Total' from V_SaleCar where Price between ? and ?");
            call.setFloat(1, price1);
            call.setFloat(2, price2);
            rs = call.executeQuery();
            while (rs.next()) {
                sum = rs.getFloat("Total");
            }
            return sum;
        } catch (SQLException ex) {
            return 0;
        }
    }

    public float TotalAir(boolean air){
          float sum = 0;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("select sum(Amount) as 'Total' from V_SaleCar where AirConditional = ?");
            call.setBoolean(1, air);

            rs = call.executeQuery();
            while (rs.next()) {
                sum = rs.getFloat("Total");
            }
            return sum;
        } catch (SQLException ex) {
            return 0;
        }
    }
     public float totalSeason(int month1,int month2){

                float sum = 0;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall( "select sum(Amount)as 'TotalS' from V_SaleCar where month(PickUpDate) between ? and ?");
            call.setFloat(1, month1);
            call.setFloat(2, month2);
            rs = call.executeQuery();
            while(rs.next()){
                sum = rs.getFloat("TotalS");
            }
            return sum;
        } catch (SQLException ex) {
           return 0;
        }
    }
}
